package custom;

import java.util.*;
import java.sql.Date;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class Applicant {
    public String first_name = "",  middle_name = "", last_name = "";
    public int id, ccid, crid, specialization_id;
    public String street, city, state, zip, area_code, phone, country_phone_code;
    public boolean submitted = false;
    private ArrayList<Degree> degrees;

    public Applicant() {
    }

    // set the degrees of the applicant
    public void setDegrees(Vector<LinkedHashMap<String, Object>> degs) {
        degrees = new ArrayList<Degree>();
        for(int i = 0; i < degs.size(); i++) {
            LinkedHashMap<String, Object> deg = degs.get(i);
            Degree degree = new Degree();
            if(deg.get("uniId") != null) {
                degree.university_id = (Integer)deg.get("uniId");
            }
            degree.university_name = (String)deg.get("uniName");

            if(deg.get("majorId") != null) {
                degree.major_id = (Integer)deg.get("majorId");
            }
            else {
                degree.major_name = (String)deg.get("majorName");
            }

            degree.uni_location = (String)(((Vector)(support.getUniversities().get((Integer)deg.get("location")))).get(0));
            int year = 1900*2, month = 0;
            try {
                year = Integer.parseInt((String)deg.get("year"));
                month = Integer.parseInt((String)deg.get("month"));
            }
            catch(Exception e) {
                System.out.println(e.getMessage());
            }
            degree.graduation_date = new Date(year-1900, month-1, 1);
            degree.gpa = Float.parseFloat((String)deg.get("gpa"));
            degree.degree_type = (String)deg.get("type");
            degrees.add(degree);
        }
    }

    public boolean submit() throws SQLException {
        if(submitted) return true; // if the user has already submitted the spplicantion, return true
        PreparedStatement insertApplicants = null;
        PreparedStatement insertUniversities = null;
        PreparedStatement insertMajors = null;
        PreparedStatement insertDegrees = null;
        Connection conn = null;
        try {
        	InitialContext cxt = new InitialContext();
            DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
            conn = ds.getConnection();
            conn.setAutoCommit(false);
            // insert the applicant and get the applicant id
            insertApplicants = conn.prepareStatement(
"INSERT INTO applicants(first_name, middle_name, last_name, ccid, crid, street, city, state, zip, area_code, phone, country_phone_code, specialization_id) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
"RETURNING id");
            insertApplicants.setString(1, first_name);
            insertApplicants.setString(2, middle_name);
            insertApplicants.setString(3, last_name);
            insertApplicants.setInt(4, ccid);
            insertApplicants.setInt(5, crid);
            insertApplicants.setString(6, street);
            insertApplicants.setString(7, city);
            insertApplicants.setString(8, state);
            insertApplicants.setString(9, zip);
            insertApplicants.setString(10, area_code);
            insertApplicants.setString(11, phone);
            insertApplicants.setString(12, country_phone_code);
            insertApplicants.setInt(13, specialization_id);
            ResultSet rs = insertApplicants.executeQuery();
            rs.next();
            id = rs.getInt(1);
            System.out.println("new applicant id:" + id);

            insertUniversities = conn.prepareStatement(
"INSERT INTO universities(country_state, university) " +
"VALUES (?, ?) " +
"RETURNING id");

            insertMajors = conn.prepareStatement(
"INSERT INTO majors(major) " +
"VALUES (?) " +
"RETURNING id");

            insertDegrees = conn.prepareStatement(
"INSERT INTO degrees(applicant_id, university_id, major_id, graduation_date, gpa, degree_type) " +
"VALUES (?, ?, ?, ?, ?, ?) ");

            // insert degrees, first insert any new universities and majors, and
            // put their ids into the corresponding degree attributes. Then insert the degree tuples
            for(int i = 0; i < degrees.size(); i++) {
                Degree deg = degrees.get(i);
                if(deg.university_id == - 1) {
                    boolean duplicate = false;
                    // find if the same university has been added before
                    for(int j = 0; j < i; j++) {
                        if(deg.university_name.equals(degrees.get(j).university_name)) {
                            duplicate = true;
                            deg.university_id = degrees.get(j).university_id;
                            System.out.println("duplicate uni name: " + deg.university_id);
                            break;
                        }
                    }
                    if(duplicate == false) {
                        System.out.println("insert new university: " + deg.uni_location +", " + deg.university_name);
                        insertUniversities.setString(1, deg.uni_location);
                        insertUniversities.setString(2, deg.university_name);
                        rs = insertUniversities.executeQuery();
                        rs.next();
                        deg.university_id = rs.getInt(1);
                    }
                }

                if(deg.major_id == -1) {
                    boolean duplicate = false;
                    for(int j = 0; j < i; j++) {
                        if(deg.major_name.equals(degrees.get(j).major_name)) {
                            duplicate = true;
                            deg.major_id = degrees.get(j).major_id;
                            System.out.println("duplicate major name: " + deg.major_id);
                            break;
                        }
                    }
                    if(duplicate == false) {
                        System.out.println("insert new majors: " + deg.major_name);
                        insertMajors.setString(1, deg.major_name);
                        rs = insertMajors.executeQuery();
                        rs.next();
                        deg.major_id = rs.getInt(1);
                    }
                }

                deg.applicant_id = id;
                System.out.println("insert new degree " + deg.toString());
                insertDegrees.setInt(1, deg.applicant_id);
                insertDegrees.setInt(2, deg.university_id);
                insertDegrees.setInt(3, deg.major_id);
                insertDegrees.setDate(4, deg.graduation_date);
                insertDegrees.setFloat(5, deg.gpa);
                insertDegrees.setString(6, deg.degree_type);
                insertDegrees.executeUpdate();
            }
            conn.commit();
        }
        catch(Exception e) {
            System.out.println(e.getMessage());
            if (conn != null) {
                try {
                    System.err.print("Transaction is being rolled back\n");
                    conn.rollback();
                } catch(SQLException excep) {
                	System.out.println("rollback exception: " + e.getMessage());
                }
            }
        	return false;
        } finally {
            if(insertApplicants != null) {
                insertApplicants.close();
            }
            if(insertUniversities != null) {
                insertUniversities.close();
            }
            if(insertMajors != null) {
                insertMajors.close();
            }
            if(insertDegrees != null) {
                insertDegrees.close();
            }
            if(conn != null) {
                conn.close();
            }
        }
        submitted = true;
        return true;
    }
}

// Degree class represents a single degree of a applicant
class Degree {
    // if the university_id or major_id is - 1, it means this is a new university or major. We need to
    // insert the name into database and get the generated id
    public int applicant_id = -1, university_id = -1, major_id = -1;
    public Date graduation_date;
    public float gpa;
    public String degree_type, uni_location;
    public String major_name = "";
    public String university_name = "";
    public String toString() {
        return applicant_id + ", " + university_id + ", " + major_id + ", " + graduation_date.toString() + ", " + gpa + ", " + degree_type;
    }
}